﻿Imports DTO
Imports System.Data.OleDb
Public Class BenhNhanDAO
    Public Shared Function LayDanhSach() As List(Of BenhNhanDTO)
        Dim ds As New List(Of BenhNhanDTO)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection
        cn = Data_Provider.ConnecDTB()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From THUOC"
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            Dim hsDto As New BenhNhanDTO
            hsDto.MaBenhNhan = dr("MaThuoc")
            hsDto.MaPhieuKhamBenh = dr("TenThuoc")
            ds.Add(hsDto)
        End While
        'B5: Dong ket noi CSDL
        dr.Close()
        cn.Close()
        Return ds
    End Function
    Public Sub CapNhatBang(ByVal dt As DataTable)
        'Kiem tra Business Rule neu co
        Dim svDao As New BenhNhanDAO()
        svDao.CapNhatBang(dt)
    End Sub
    Public Shared Function LayBang() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Data_Provider.ConnecDTB()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select  kb.HoTen ,kb.NgayKham,tc.TenTrieuChung,lb.TenLoaiBenh"

        strSQL = strSQL & " From BENHNHAN bn, TRIEUCHUNG tc, KHAMBENH kb , PHIEUKHAMBENH pkb ,LOAIBENH lb"

        strSQL = strSQL & " where bn.MaPhieuKhamBenh = pkb.MaPhieuKhamBenh and bn.MaKhamBenh = kb.MaKhamBenh "

        strSQL = strSQL & " and bn.MaLoaiBenh =lb.MaLoaiBenh and tc.MaTrieuChung=bn.MaTrieuChung "


        strSQL = strSQL & " group by  kb.HoTen ,kb.NgayKham,tc.TenTrieuChung,lb.TenLoaiBenh"
        'B4: Thuc thi chuoi strSQL
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function
End Class
